import { query } from '../../config/db.js';

/**
 * 寻宠帖子数据模型
 */
export class Post {
  constructor(postData) {
    this.id = postData.id;
    this.user_id = postData.user_id;
    this.pet_id = postData.pet_id;
    this.last_seen_location = postData.last_seen_location;
    this.last_seen_time = postData.last_seen_time;
    this.post_status = postData.post_status;
    this.admin_status = postData.admin_status;
    this.video_url = postData.video_url;
    this.contact_info = postData.contact_info;
    this.created_at = postData.created_at;
    this.updated_at = postData.updated_at;
    
    // 关联数据
    this.pet_name = postData.pet_name;
    this.pet_species = postData.pet_species;
    this.pet_breed = postData.pet_breed;
    this.pet_color = postData.pet_color;
    this.pet_gender = postData.pet_gender;
    this.pet_photo_url = postData.pet_photo_url;
    this.pet_description = postData.pet_description;
    this.owner_username = postData.owner_username;
    this.owner_email = postData.owner_email;
    this.sightings_count = postData.sightings_count;
  }

  /**
   * 创建新帖子
   * @param {Object} postData - 帖子数据
   * @returns {Promise<Object>} 创建的帖子信息
   */
  static async create(postData) {
    const { 
      user_id, 
      pet_id, 
      last_seen_location, 
      last_seen_time, 
      video_url, 
      contact_info 
    } = postData;
    
    const sql = `
      INSERT INTO posts (user_id, pet_id, last_seen_location, last_seen_time, video_url, contact_info)
      VALUES (?, ?, ?, ?, ?, ?)
    `;
    
    try {
      const result = await query(sql, [
        user_id, 
        pet_id, 
        last_seen_location, 
        last_seen_time, 
        video_url || null, 
        contact_info || null
      ]);
      
      // 返回新创建的帖子信息
      const newPost = await Post.findById(result.insertId);
      return newPost;
    } catch (error) {
      console.error('创建帖子失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找帖子（包含关联信息）
   * @param {number} id - 帖子ID
   * @returns {Promise<Object|null>} 帖子信息或null
   */
  static async findById(id) {
    const sql = `
      SELECT 
        p.*,
        pet.name as pet_name,
        pet.species as pet_species,
        pet.breed as pet_breed,
        pet.color as pet_color,
        pet.gender as pet_gender,
        pet.photo_url as pet_photo_url,
        pet.description as pet_description,
        u.username as owner_username,
        u.email as owner_email,
        (SELECT COUNT(*) FROM sightings s WHERE s.post_id = p.id) as sightings_count
      FROM posts p
      LEFT JOIN pets pet ON p.pet_id = pet.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE p.id = ?
    `;
    
    try {
      const results = await query(sql, [id]);
      return results.length > 0 ? new Post(results[0]) : null;
    } catch (error) {
      console.error('查找帖子失败:', error);
      throw error;
    }
  }

  /**
   * 获取帖子列表（支持筛选和分页）
   * @param {Object} filters - 筛选条件
   * @param {Object} options - 查询选项
   * @returns {Promise<Array>} 帖子列表
   */
  static async findAll(filters = {}, options = {}) {
    const {
      post_status,
      admin_status,
      species,
      color,
      gender,
      location,
      keyword,
      dateFrom,
      dateTo,
      user_id
    } = filters;
    const { page = 1, limit = 10, orderBy = 'created_at', orderDir = 'DESC' } = options;
    const offset = (page - 1) * limit;

    let sql = `
      SELECT
        p.*,
        pet.name as pet_name,
        pet.species as pet_species,
        pet.breed as pet_breed,
        pet.color as pet_color,
        pet.gender as pet_gender,
        pet.photo_url as pet_photo_url,
        pet.description as pet_description,
        u.username as owner_username,
        (SELECT COUNT(*) FROM sightings s WHERE s.post_id = p.id) as sightings_count
      FROM posts p
      LEFT JOIN pets pet ON p.pet_id = pet.id
      LEFT JOIN users u ON p.user_id = u.id
      WHERE 1=1
    `;
    const params = [];

    // 添加筛选条件
    if (post_status) {
      sql += ' AND p.post_status = ?';
      params.push(post_status);
    }

    if (admin_status) {
      sql += ' AND p.admin_status = ?';
      params.push(admin_status);
    }

    if (species) {
      sql += ' AND pet.species LIKE ?';
      params.push(`%${species}%`);
    }

    if (color) {
      sql += ' AND pet.color LIKE ?';
      params.push(`%${color}%`);
    }

    if (gender) {
      sql += ' AND pet.gender = ?';
      params.push(gender);
    }

    if (location) {
      sql += ' AND p.last_seen_location LIKE ?';
      params.push(`%${location}%`);
    }

    if (keyword) {
      sql += ' AND (pet.name LIKE ? OR pet.description LIKE ? OR p.last_seen_location LIKE ? OR pet.breed LIKE ?)';
      const keywordPattern = `%${keyword}%`;
      params.push(keywordPattern, keywordPattern, keywordPattern, keywordPattern);
    }

    if (dateFrom) {
      sql += ' AND DATE(p.last_seen_time) >= ?';
      params.push(dateFrom);
    }

    if (dateTo) {
      sql += ' AND DATE(p.last_seen_time) <= ?';
      params.push(dateTo);
    }

    if (user_id) {
      sql += ' AND p.user_id = ?';
      params.push(user_id);
    }

    // 添加排序和分页
    sql += ` ORDER BY p.${orderBy} ${orderDir} LIMIT ${parseInt(limit)} OFFSET ${parseInt(offset)}`;

    try {
      const results = await query(sql, params);
      return results.map(post => new Post(post));
    } catch (error) {
      console.error('获取帖子列表失败:', error);
      throw error;
    }
  }

  /**
   * 获取帖子总数
   * @param {Object} filters - 筛选条件
   * @returns {Promise<number>} 帖子总数
   */
  static async count(filters = {}) {
    const {
      post_status,
      admin_status,
      species,
      color,
      gender,
      location,
      keyword,
      dateFrom,
      dateTo,
      user_id
    } = filters;

    let sql = `
      SELECT COUNT(*) as count
      FROM posts p
      LEFT JOIN pets pet ON p.pet_id = pet.id
      WHERE 1=1
    `;
    const params = [];

    // 添加筛选条件
    if (post_status) {
      sql += ' AND p.post_status = ?';
      params.push(post_status);
    }

    if (admin_status) {
      sql += ' AND p.admin_status = ?';
      params.push(admin_status);
    }

    if (species) {
      sql += ' AND pet.species LIKE ?';
      params.push(`%${species}%`);
    }

    if (color) {
      sql += ' AND pet.color LIKE ?';
      params.push(`%${color}%`);
    }

    if (gender) {
      sql += ' AND pet.gender = ?';
      params.push(gender);
    }

    if (location) {
      sql += ' AND p.last_seen_location LIKE ?';
      params.push(`%${location}%`);
    }

    if (keyword) {
      sql += ' AND (pet.name LIKE ? OR pet.description LIKE ? OR p.last_seen_location LIKE ? OR pet.breed LIKE ?)';
      const keywordPattern = `%${keyword}%`;
      params.push(keywordPattern, keywordPattern, keywordPattern, keywordPattern);
    }

    if (dateFrom) {
      sql += ' AND DATE(p.last_seen_time) >= ?';
      params.push(dateFrom);
    }

    if (dateTo) {
      sql += ' AND DATE(p.last_seen_time) <= ?';
      params.push(dateTo);
    }

    if (user_id) {
      sql += ' AND p.user_id = ?';
      params.push(user_id);
    }

    try {
      const results = await query(sql, params);
      return results[0].count;
    } catch (error) {
      console.error('获取帖子总数失败:', error);
      throw error;
    }
  }

  /**
   * 更新帖子信息
   * @param {number} id - 帖子ID
   * @param {Object} updateData - 要更新的数据
   * @returns {Promise<Object|null>} 更新后的帖子信息
   */
  static async update(id, updateData) {
    const allowedFields = [
      'last_seen_location', 
      'last_seen_time', 
      'post_status', 
      'video_url', 
      'contact_info'
    ];
    const updateFields = [];
    const updateValues = [];

    // 只允许更新指定字段
    for (const field of allowedFields) {
      if (updateData[field] !== undefined) {
        updateFields.push(`${field} = ?`);
        updateValues.push(updateData[field]);
      }
    }

    if (updateFields.length === 0) {
      throw new Error('没有可更新的字段');
    }

    updateValues.push(id);
    const sql = `
      UPDATE posts 
      SET ${updateFields.join(', ')}, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;

    try {
      await query(sql, updateValues);
      return await Post.findById(id);
    } catch (error) {
      console.error('更新帖子失败:', error);
      throw error;
    }
  }

  /**
   * 更新帖子审核状态（管理员用）
   * @param {number} id - 帖子ID
   * @param {string} adminStatus - 审核状态
   * @returns {Promise<Object|null>} 更新后的帖子信息
   */
  static async updateAdminStatus(id, adminStatus) {
    const sql = `
      UPDATE posts 
      SET admin_status = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `;

    try {
      await query(sql, [adminStatus, id]);
      return await Post.findById(id);
    } catch (error) {
      console.error('更新帖子审核状态失败:', error);
      throw error;
    }
  }

  /**
   * 获取帖子相关的线索列表
   * @param {number} id - 帖子ID
   * @returns {Promise<Array>} 线索列表
   */
  static async getSightings(id) {
    const sql = `
      SELECT id, sighting_photo_url
      FROM sightings
      WHERE post_id = ?
    `;

    try {
      const sightings = await query(sql, [id]);
      return sightings;
    } catch (error) {
      console.error('获取帖子线索失败:', error);
      throw error;
    }
  }

  /**
   * 删除帖子
   * @param {number} id - 帖子ID
   * @returns {Promise<boolean>} 是否删除成功
   */
  static async delete(id) {
    const sql = 'DELETE FROM posts WHERE id = ?';

    try {
      const result = await query(sql, [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除帖子失败:', error);
      throw error;
    }
  }

  /**
   * 检查帖子是否属于指定用户
   * @param {number} postId - 帖子ID
   * @param {number} userId - 用户ID
   * @returns {Promise<boolean>} 是否属于该用户
   */
  static async belongsToUser(postId, userId) {
    const sql = 'SELECT COUNT(*) as count FROM posts WHERE id = ? AND user_id = ?';
    
    try {
      const results = await query(sql, [postId, userId]);
      return results[0].count > 0;
    } catch (error) {
      console.error('检查帖子所有权失败:', error);
      throw error;
    }
  }

  /**
   * 获取帖子统计信息
   * @returns {Promise<Object>} 统计信息
   */
  static async getStats() {
    const sql = `
      SELECT 
        COUNT(*) as total_posts,
        COUNT(CASE WHEN post_status = 'searching' THEN 1 END) as searching_posts,
        COUNT(CASE WHEN post_status = 'found' THEN 1 END) as found_posts,
        COUNT(CASE WHEN admin_status = 'pending' THEN 1 END) as pending_posts,
        COUNT(CASE WHEN admin_status = 'approved' THEN 1 END) as approved_posts,
        COUNT(CASE WHEN admin_status = 'rejected' THEN 1 END) as rejected_posts,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_posts,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as week_posts
      FROM posts
    `;

    try {
      const results = await query(sql);
      return results[0];
    } catch (error) {
      console.error('获取帖子统计失败:', error);
      throw error;
    }
  }

  /**
   * 转换为JSON格式
   * @returns {Object} 帖子信息
   */
  toJSON() {
    return {
      id: this.id,
      user_id: this.user_id,
      pet_id: this.pet_id,
      last_seen_location: this.last_seen_location,
      last_seen_time: this.last_seen_time,
      post_status: this.post_status,
      admin_status: this.admin_status,
      video_url: this.video_url,
      contact_info: this.contact_info,
      created_at: this.created_at,
      updated_at: this.updated_at,
      pet: {
        name: this.pet_name,
        species: this.pet_species,
        breed: this.pet_breed,
        color: this.pet_color,
        gender: this.pet_gender,
        photo_url: this.pet_photo_url,
        description: this.pet_description
      },
      owner: {
        username: this.owner_username,
        email: this.owner_email
      },
      sightings_count: this.sightings_count || 0
    };
  }
}
